<?php

namespace common\models\dbViews;

use common\datas\Enums;
use common\models\db\Skus;
use common\models\db\Spus;
use yii\web\View;

class ViewSkus extends \yii\db\ActiveRecord
{
    public static function tableName()
    {
        return '{{%view_skus}}'; // 视图名称
    }

    public static function viewUp()
    {
        $tableNameViewSkus = \Yii::$app->db->schema->getRawTableName(static::tableName());
//        $tableNameSpus = \Yii::$app->db->schema->getRawTableName(Spus::tableName());
//        \Yii::$app->db->createCommand("ALTER TABLE `$tableNameSpus` ADD INDEX `idx_append_remote_freight_province_ids` (CAST(`append_remote_freight_province_ids` AS UNSIGNED ARRAY));")->execute();
//        \Yii::$app->db->createCommand("CREATE INDEX IF NOT EXISTS `idx_append_remote_freight_province_ids` ON `$tableNameSpus` (
//    (CAST(JSON_EXTRACT(`append_remote_freight_province_ids`, '$[0]') AS UNSIGNED ARRAY))
//);")->execute();
//        \Yii::$app->db->createCommand("ALTER TABLE `$tableNameSpus` ADD FULLTEXT INDEX `idx_ft_keywords` ((CAST(tags->'$[*]' AS CHAR(20) ARRAY));")->execute();
//        \Yii::$app->db->createCommand("CREATE INDEX IF NOT EXISTS `idx_ft_keywords` ON `$tableNameSpus` ((CAST(tags->'$[*]' AS CHAR(20) ARRAY));")->execute();
        $query = Skus::find()->alias('sku');
        $query->joinWith(['coverFile' => function ($q) {
            $q->alias('file');
        }]);
        $query->joinWith(['spu' => function ($q) {
            $q->alias('spu');
            $q->andFilterWhere(['spu.status' => Enums::ACTIVE]);
            $q->andFilterWhere(['spu.is_put_on_sale' => Enums::YES]);
//            $q->joinWith(['remoteFreights' => function ($q1) {
//                $q1->alias('remoteFreights');
//            }]);
        }], true, 'RIGHT JOIN');
        $query->joinWith(['hasAttrvs' => function ($q) {
            $q->alias('hasAttrvs');
            $q->joinWith(['skuAttrv' => function ($q1) {
                $q1->alias('skuAttrv');
                $q1->joinWith(['skuAttrk' => function ($q2) {
                    $q2->alias('skuAttrk');
                }]);
            }]);
        }]);
//        $query->joinWith(['remoteFreights' => function ($q) {
//            $q->alias('remoteFreights');
//        }]);

        $query->orderBy(['sku.spu_id' => SORT_DESC, 'sku.sort' => SORT_ASC]);
        $query->select([
            'sku.*',
            'spu_name' => 'spu.name',
            'spu_numbering' => 'spu.numbering',
            'spu_inventory' => 'spu.inventory',
            'spu_keywords' => 'spu.keywords',
            'spu_sort' => 'spu.sort',
        ]);
        $res = $query->asArray()->all();
        foreach ($res as $k => $v) {
            dd($v);
        }
        $sql = $query->createCommand()->rawSql;
        $sql = "create view `$tableNameViewSkus` as ( $sql )";
        \Yii::$app->db->createCommand($sql)->execute();
    }

    public static function viewDown()
    {
        $tableName = \Yii::$app->db->schema->getRawTableName(static::tableName());
        \Yii::$app->db->createCommand("DROP VIEW IF EXISTS $tableName")->execute();
    }
}